Methods and systems for hardware acceleration of streamed database operations and queries based on multiple hardware accelerators

ABSTRACT

Embodiments of the present invention provide a hardware accelerator that assists a host database system in processing its queries. The hardware accelerator comprises special purpose processing elements that are capable of receiving database query/operation tasks in the form of machine code database instructions, execute them in hardware without software, and return the query/operation result back to the host system.

CROSS-REFERENCE TO RELATED APPLICATIONS

This application claims priority to U.S. Provisional Patent ApplicationNo. 61/359,407, filed on Jun. 29, 2010, entitled Methods and Systems forHardware Acceleration of Operations and Queries based on MultipleHardware Accelerators, which is herein incorporated by reference in itsentirety. This application is related to the following U.S. PatentApplications and Patents, which are herein incorporated by reference intheir entirety: U.S. patent application Ser. No. 11/895,952, filed onAug. 27, 2007, entitled Methods and Systems for Hardware Acceleration ofDatabase Operations and Queries, by Joseph I. Chamdani et al.; U.S.patent application Ser. No. 11/895,998, filed on Aug. 27, 2007, entitledHardware Acceleration Reconfigurable Processor for Accelerating DatabaseOperations and Queries, by Jeremy Branscome et al.; U.S. patentapplication Ser. No. 11/895,997, filed on Aug. 27, 2007, entitledProcessing Elements of a Hardware Acceleration Reconfigurable Processorfor Accelerating Database Operations and Queries, by Jeremy Branscome etal.; U.S. patent application Ser. No. 12/144,486, filed on Jun. 23,2008, entitled Methods and Systems for Real-time Continuous Updates, byKapil Surlaker et al.; U.S. patent application Ser. No. 12/099,131,filed on Apr. 7, 2008, entitled Accessing Data in a Column StoreDatabase Based on Hardware Compatible Data Structures, by Liuxi Yang etal.; U.S. patent application Ser. No. 12/099,133, filed on Apr. 7, 2008,entitled Accessing Data in a Column Store Database Based on HardwareCompatible Data Structures, by Liuxi Yang et al.; U.S. patentapplication Ser. No. 12/099,133, filed on Apr. 7, 2008, entitledAccessing Data in a Column Store Database Based on Hardware CompatibleIndexing and Replicated Reordered Columns, by Krishnan Meiyyappan etal.; and U.S. patent application Ser. No. 12/144,303, filed on Jun. 23,2008, entitled Fast Bulk Loading and Incremental Loading of Data into aDatabase, by James Shau et al.

FIELD

This invention relates generally to database systems. More particularly,it relates to database systems that are optimized by using hardwareacceleration.

BACKGROUND

Despite their different uses, applications, and workloadcharacteristics, most systems run on a common Database Management System(DBMS) using a standard database programming language, such asStructured Query Language (SQL). Most modern DBMS implementations(Oracle, IBM DB2, Microsoft SQL, Sybase, MySQL, PostgreSQL, Ingress,etc.) are implemented on relational databases, which are well known tothose skilled in the art.

Typically, a DBMS has a client side where applications or users submittheir queries and a server side that executes the queries. On the serverside, most enterprises employ one or more general-purpose servers.However, although these platforms are flexible, general-purpose serversare not optimized for many enterprise database applications. In ageneral purpose database server, all SQL queries and transactions areeventually mapped to low level software instructions called assemblyinstructions, which are then executed on a general purposemicroprocessor (CPU). The CPU executes the instructions, and its logicis busy as long as the operand data are available, either in theregister file or on-chip cache. To extract more parallelism from theassembly code and keep the CPU pipeline busy, known CPUs attempt topredict ahead the outcome of branch instructions and execute down thecode path speculatively. Execution time is reduced if the speculation iscorrect; the success of this speculation, however, is data dependent.Other state-of-the-art CPUs attempt to increase performance by employingsimultaneous multithreading (SMT) and/or multi-core chip multiprocessing(CMP). To take advantage of these, changes have to be made at theapplication or DBMS source code to manually create the process/threadparallelism for the SMT or CMP CPUs. This is generally considered highlyas very complex to implement and not always applicable to generalpurpose CPUs because it is workload dependent.

Unfortunately, general purpose CPUs are not efficient for databaseapplications. Branch prediction is generally not accurate becausedatabase processing involves tree traversing and link list or pointerchasing that is very data dependent. Known CPUs employ the well-knowninstruction-flow (or Von Neumann) architecture, which uses a highlypipelined instruction flow (rather than a data-flow where operand datais pipelined) to operate on data stored in the CPUs tiny register files.Real database workloads, however, typically require processing Gigabytesto Terabytes of data, which overwhelms these tiny registers with loadsand reloads. On-chip cache of a general purpose CPU is not effectivesince it's relatively too small for real database workloads. Thisrequires that the database server frequently retrieve data from itsrelatively small memory or long latency disk storage. Accordingly, knowndatabase servers rely heavily on squeezing the utilization of theirsmall system memory size and disk input/output (I/O) bandwidth. Thoseskilled in the art recognize that these bottlenecks between storage I/O,the CPU, and memory are very significant performance factors.

However, overcoming these bottlenecks is a complex task because typicaldatabase systems consist of several layers of hardware, software, etc.,that influence the overall performance of the system. These layerscomprise, for example, the application software, the DBMS software,operating system (OS), server processor systems, such as its CPU,memory, and disk I/O and infrastructure. Traditionally, performance hasbeen optimized in a database system “horizontally,” i.e., within aparticular layer. For example, many solutions attempt to optimizevarious solutions for the DBMS query processing, caching, the disk I/O,etc. These solutions employ a generic, narrow approach that still failsto truly optimize the large performance potentials of the databasesystem, especially for relational database systems having complexread-intensive applications.

Accordingly, it would be very desirable to provide a more completesolution for database systems than what is currently available.

BRIEF DESCRIPTION OF THE DRAWINGS

The accompanying drawings, which are incorporated in and constitute apart of this specification, illustrate embodiments of the invention andtogether with the description, serve to explain the principles of theinvention. In the figures:

FIG. 1 illustrates an exemplary system that is consistent with theprinciples of the present invention;

FIGS. 2A, 2B, and 2C illustrate exemplary system topologies that areconsistent with the principles of the present invention;

FIG. 3 illustrates an exemplary functional architecture of a systemconsistent with the principles of the present invention;

FIG. 4 illustrates an exemplary protocol stack employed by embodimentsof the present invention;

FIG. 5 illustrates an exemplary Query Software Module (QSM) that isconsistent with the principles of the present invention;

FIG. 6 conceptually illustrates how tasks for one or more queries may beprocessed by an embodiment of the present invention;

FIG. 7 illustrates an exemplary dataflow for a query by an embodiment ofthe present invention;

FIG. 8 illustrates exemplary use cases by an embodiment of the presentinvention;

FIG. 9 illustrates an exemplary execution by a QSM and interface betweenthe QSM and a MOP;

FIG. 10 conceptually illustrates the difference between instruction-flowarchitecture machines versus data-flow architecture machines;

FIG. 11 illustrates an exemplary benefit of data-flow architecturemachines in reducing I/O and memory resource bottlenecks;

FIG. 12 illustrates an exemplary benefit of a group index and pipelineparallelism employed by embodiments of the present invention;

FIG. 13 illustrates an exemplary use of snapshot version vectormanagement employed by embodiments of the present invention;

FIG. 14 illustrates an exemplary use of snapshot version vectors with ahitlist employed by embodiments of the present invention;

FIG. 15 illustrates an exemplary architecture for integratingembodiments of the present invention with a relational or row-store (RS)type of database management system;

FIG. 16 illustrates yet another exemplary architecture for integratingembodiments of the present invention with a database management system;

FIG. 17 illustrates further details of a PHYSTACK used by embodiments ofthe present invention;

FIG. 18 shows an example of hardware partitioning by an embodiment ofthe present invention;

FIG. 19 shows an example of partition consumption according to anembodiment of the present invention; and

FIG. 20 shows an example of a hardware hash join according to anembodiment of the present invention.

DESCRIPTION OF THE EMBODIMENTS

In order to accelerate query processing, embodiments of the presentinvention may analyze a query and break it down into its tasks. Theembodiments may then use hardware execution resources or softwareexecution resources to process the query. The hardware executionresources, referred to as query processing modules (QPMs), may utilizedatabase machine code instructions known as MOPs to perform a particulartask of a query. The software execution resources, referred to as querysoftware modules (QSMs), are implemented in software and may utilizesoftware operations known as SOPs to perform their tasks of the query.To help illustrate the embodiments, the distinction between softwareexecution of SOPs and hardware execution of MOPs may now be furtherdescribed.

A SOP is one or more instructions that are executed with software. Thatis, a SOP is executed by a QSM running on a general-purpose CPU, such asan x86 processor from Intel. In addition, the QSM providescommunications interface for coordination with other tasks beingperformed by another resource, such as one or more QSMs or one or moreother QPMs.

Alternatively, a MOP is one or more instructions that are executed withhardware. In particular, a MOP is executed in hardware as databasemachine code instructions on custom hardware, such as a HARP, in what isreferred to as a Query Processing Module (QPM). Such custom hardware isalso described in the related applications, which are incorporated byreference.

When processing a query, the QPMs and QSMs may cooperate with each otherto execute the task in parallel, or in a pipelined fashion to expediteprocessing.

One embodiment relates to utilizing multiple hardware accelerators toassist in query processing. In particular, a system may comprise: atleast one BSM running a database management system; a set of queryprocessing modules (QPM) each coupled to the at least one BSM. A QPM isimplemented using custom hardware and provides a physical executionenvironment for database machine code instructions, such asMacro-Operations (MOPs). The QPMs may be interconnected to allow forallowing concurrent, parallel communication between any host and anyQPM. The system may use fast storage other than disk (e.g. SSDs, RAMappliances, flash appliances, etc.). The QPMs may be interconnected thatare a combination of PCIe, IB, GIGE, HT, SATA, SAS, or another protocol.

In addition to the QPMs, one or more BSMs may comprise software modulesto assist in processing a query. For purposes of brevity, the presentdisclosure shall refer to these software-based resources as querysoftware modules (QSM). A QSM may be implemented within the BSM or onone or more servers, called Compute Server Modules (CSMs), which arecoupled to the BSM. In addition, a particular hardware platform, such asthe host BSM or a CSM, may comprise multiple QSMs.

A QSM is a module (virtual machine) that is implemented in software andfunctions as an execution engine for software operations (SOPs), whichprovides a virtual environment in which the SOPs can execute. In someembodiments, a QSM can be viewed as a software-equivalent to a QPM. AQSM also provides a means of I/O for MOPs and SOPs.

In the embodiments, a QSM may comprise a base server (herein a BSM),i.e., a general-purpose computer or server, which is configured withsoftware to implement the QSM virtual machine. Of note, such a BSM or aCSM may implement multiple virtual QSMs. In addition, each BSM and CSMcan have their own hardware and network connections.

To facilitate the sharing of information, memory may be shared betweenQPMs and QSMs. In one embodiment, a system implements shared memoryamong the dataflow processing elements, such that any given dataflowprocessing element has access to the same memory as any other dataflowprocessing element.

Moreover, processing flows may be shared between multiple QPMs and QSMs.For example, the processing output of a first QPM may be fed to a secondQPM or a QSM, and vice versa. In one embodiment, a system implements ashared flow among the dataflow processing elements, such that any givenquery processing resource, i.e., a QPM or QSM, may transmit its flow toat least one of any other query processing resource. Those skilled inthe art will understand that a QPM or QSM may transmit multiple flows,i.e., based on multiple SOPs or MOPS. These embodiments may have severaladvantages and features.

Of note, FIG. 10 helps illustrate the concept of a dataflow as employedin the embodiments of the present disclosure. As shown, with a data-flowmachine, intermediate query results do not need to be materialized inmemory or on disk. In addition, an entire query from data to result mayexecute as dataflow within data-flow machines as described by thepresent disclosure.

In some embodiments, the processing by the data-flow machines are fullypipelined for high concurrency and throughput. On-chip flow controlbuffering may be used to eliminate memory traffic. In addition, initialdisk read and final result are then communicated to a databasemanagement system.

In some instances, the data-flow architecture of the present embodimentsenables reduction in I/O and improves performance equivalent to a largecluster of CPU cores, such as those utilized in conventional system.

FIG. 11 illustrates an exemplary benefit of data-flow architecturemachines in reducing I/O and memory resource bottlenecks. As shown, MOPthreads may participate fairly in accessing index memory and execution.Data can be executed fairly by availability, e.g, in a non-blockingfashion.

Furthermore, in some embodiments, MOP threads may prefetch with dataflowlocality and MOP threads may lookahead. In various embodiments, packednodes contain relevant RIDs and no additional index locality is requiredor assumed.

For purposes of brevity, the following abbreviations are used in thisdisclosure.

-   -   STSD (Single Task Single Data): One QPM, one task on same        portion of data.    -   STMD (Single Task Multi Data): One QPM, one task, executed        iteratively, consuming multiple distinct portions of data.    -   MTMD (Multi Task Multi Data): One QPM, multiple tasks, executing        in pipelined, parallel fashion consuming multiple distinct        portions of data.    -   SPSF (Single Program Single Flow): Multiple QPMs, at least one        task per (all tasks from same query), executing in pipelined,        parallel fashion, where any given QPM is configured to execute        in either STSD, STMD, or MTMD.    -   SPMF (Single Program Multi Flow): Multiple QPMs, at least one        task per (all tasks from same query), executing in parallel        fashion, where any given QPM is configured to execute in either        STSD, STMD, and/or MTMD.    -   MPSF (Multi Program Single Flow): Multiple QPMs, at least one        task per (at least two programs represented), executing in        independent pipelined, parallel fashion, where each program is        being executed as SPSF.    -   MPMF (Multi Program Multi Flow): Multiple QPMs, at least one        task per (at least two programs represented), executing in        parallel fashion, where each query is being executed as SPMF.

Hardware multi-tasking enables MTMD for at least one query (program) ona single QPM. Hardware system partitioned execution allows multipletasks to be executed concurrently on one or more independent QPMparallel execution of MPMF.

Hierarchical aggregation that permits partial aggregation of results andwhere the results are transferred (utilizing hardware system pipelining)from one QPM or host to another QPM or host and further aggregated so asto minimize the number of transfers required. Hardware task pipeliningpermits any individual QPM to execute a query plan operation overdistinct portions of database data that implements STMD and MTMD.

In the embodiments, the resources of the system may be pipelined. Thatis, processing resources, i.e., QSMs or QPMs, may be configured in asequence, so that the output of one resource as the input of the nextresource and so forth. Pipelining may occur between software resources,i.e., QSM-QSM, hardware resource, i.e., QPM-QPM, and mixed resources,such as QSM-QPM or QPM-QSM.

Software system pipelining such that multiple QPMs can transfer multipledataflows to a QSM, and/or a QSM can transfer multiple dataflows tomultiple QPMs. Such pipelining can provide pipelined execution ofdatabase operations on dataflow (filter, join, aggregate, etc.),pipelined execution of SPSF involving multi-QPM and multi-host,execution of database operations on portions of database (from storage),and disk filtering.

Portions of the database needed for subsequent execution may also bedynamically discovered. For example, only the portions of the databasethat are necessary are fetched by snooping execution and preparing datafor subsequent tasks

Hardware partitioning is provided to allow one or more QPMs to implementmachine database code execution capable of partitioning a portion ofmultiple database tables or indexes according to an optimizer-determinedmapping of partitions, which may then optimize query plan execution inSPMF and MPMF.

Static index partitioning enables indexes that are staticallypartitioned and stored according to pre-determined optimal executionlocality for SPMF and MPMF. In some embodiments, these are managedindependently and can include an update portion of the index, which isnot necessarily partitioned. Static data partitioning is where databasetables are statically partitioned and stored according to pre-determinedoptimal execution locality for SPMF and MPMF.

Mixed mode execution may be optimized wherein some QPMs execute SPSF(for pipeline parallelism), while other QPMs execute SPMF (for dataparallelism) in a manner which optimizes execution. This can includeMPSF and MPMF when more than one query is involved in concurrentexecution.

Hardware system pipelining enables multiple live dataflow transmissionfrom one QPM to multiple QPMs, and/or from multiple hosts to a QPM,and/or from multiple QPM to a host to implement SPSF (and MPSF or MPMF),provide partitioning/repartitioning, and dynamicdistribution/redistribution of database data in a manner, whichoptimizes parallel query plan execution on SPMF or MPMF. One skilled inthe art will recognize that a QPM can emit multiple flows to anotherQPM, and likewise, a QPM can emit multiple flows to multiple QPMs.

In other embodiments, query execution planning can be based on the useof multiple hardware accelerators. In some embodiments, algorithms forutilizing multiple QPMs take advantage of their bulk processingcapability.

The present disclosure relates to various embodiments for acceleratingquery processing. In one embodiment, a database hardware compute nodecan be scaled up using, for example, a hybrid configuration of multipleQPMs, QSMs, shared SAMs, dual BSMs, and shared flow concept. The scalingup may be performed using a multi-QPM architecture with shared flows;static and dynamic partitioning of base table columns and indexes acrossQPMs/QSMs; pipelining of MOP tasks, SOP tasks, MOP-SOP mixed tasks; taskscheduling and memory management across multiple QPMs; and using methodsto optimize a query plan based on scaled up nodes.

In addition, the present disclosure relates to streaming integration ofSQL chip in row store DBMS. For example, methods are provided forstreaming acceleration at row block level, such as MOPs to acceleratehybrid row store conversion, compression/decompression,encryption/decryption, etc. Other methods are provided for streamingacceleration at the SQL operator level. For example, MOPs are providedfor projection, predicate filtering, group by aggregation, order bylimit, hash join, sorted merge join, merge sort, analytics function,etc.

Dataflow integration of SQL chip and column store in MPP-based row storeDBMS are also provided in the present disclosure. For example, streamingacceleration with partial dataflow of a logical SQL operator isprovided, such as a TableScan operator can be accelerated using apipeline that is based on a decryption MOP, a decompression MOP, acolumn projection MOP, and a redicate filtering MOP. Caching of hotcolumns and intermediate results and methods to update the cache withACID compliance are also provided. Furthermore, methods are provided tooptimize a query plan across MPP nodes having dataflow SQL chipacceleration, such as, collocated query fragments that have beendispatched to an MPP node. Methods and systems may be provided tominimize data exchanges across MPP node via hardware-based

Hardware-based snapshot versioning is also provided in the presentdisclosure. For example, MVCC-like support is provided forquery-while-load capability. Timestamps may be represented via asnapshot version number (svn) in snapshot bitmap vectors. Methods areprovided to keep track of committed versus uncommitted rows. Snapshotrefresh can be accelerated via a MOP. Furthermore, a query plan can beformulated with svn filtering using snapshot bitmap vectors.

The present invention employs a custom computing (C2) solution thatprovides a significant gain in performance for enterprise databaseapplications. In the C2 solution, a node or appliance may comprise thehost (or base) system that is combined with a query processor module(QPM). These QPMs may comprise one or more hardware acceleratedreconfigurable processors (HARP). These HARPs in a QPM are speciallydesigned to optimize the performance of database systems and itsapplications, especially relational database systems and read-intensiveapplications.

A host system may be any standard or pre-existing DBMS system. Ingeneral, such systems may comprise a standard general purpose CPU, asystem memory, I/O interfaces, etc. In some embodiments, a host systemmay be referred to as a base server module, i.e., a “BSM”.

The QPMs are coupled to the BSM and are designed to offload repetitivedatabase operations from the DBMS running on the BSM. The QPMs utilizedataflow architecture processing elements that execute machine codeinstructions that are defined for various database operations, which maybe organized using macro-operations (MOPs). The C2 solution may employ anode that is scalable to include one QPM, or multiple QPMs. In addition,the C2 solution may use a federated architecture comprising multiplenodes, i.e., multiple DBMS servers that are enhanced with the C2solution.

In some embodiments, the C2 solution employs an open architecture andco-processor approach so that the C2 hardware can be easily integratedinto existing database systems. Of note, the hardware acceleration ofthe C2 solution utilizes novel machine code database instructions toexecute certain fragments of a query in a dataflow and using parallel,pipelined execution.

In the present invention, the C2 solution also comprises software thatorchestrates the operations of the DBMS running on the BSM and the QPMs.The C2 software is configured with a flexible, layered architecture tomake it hardware and database system agnostic. Thus, the C2 software iscapable of seamlessly working with existing DBMSs based on this openarchitecture.

In general, the C2 software receives the query from the DBMS and breaksthe query down into query fragments. The C2 software then decides whichof these query fragments can be appropriately handled in software (inthe C2 software itself or back in the originating DBMS) or, ideally,with hardware acceleration in the QPMs. All or part of the query may beprocessed by the C2 software and QPMs.

In addition, in order to maximize the efficiency of the hardwareacceleration, the C2 solution stores its databases in compressed,column-store format and utilizes various hardware-friendly datastructures. The C2 solution may employ various compression techniques tominimize or reduce the storage footprint of its databases. Thecolumn-store format and hardware-friendly data structures allow the QPMsor C2 software to operate directly on the compressed data in thecolumn-store database. The column-store database may employ columns andcolumn groups that are arranged based on an implicit row identifier(RID) scheme and RID to primary column to allow for easy processing bythe QPMs. The hardware-friendly data structures also allow for efficientindexing, data manipulation, etc. by the QPMs. Such hardware friendlydata structures are described also in the related applications, whichare herein incorporated by reference in their entirety.

For example, the C2 solution utilizes a global virtual address space forthe entire database to greatly simplify and maximize efficiency ofcreate, read, update, and delete operations of data in a database. Insome embodiments, the columns and column groups are configured with afixed width to allow for arithmetic memory addressing and translationfrom a virtual address to a physical memory address. On-demand andspeculative prefetching may also be utilized by the C2 solution to hideI/O latency and maximize QPM utilization. Various indexing structuresthat are optimized for hardware execution of query fragments are alsoemployed in the C2 software 110.

In some embodiments, the processing elements may share the same addressspace as one another. This feature allows various QPMs to share databetween processing elements as well as share data between processingelements in other QPMs.

Due to the comprehensive nature of the present inventions in the C2solution, the figures are presented generally from a high level ofdetail and progress to a low level of detail. For example, FIGS. 1, 2A,and 2B illustrate an exemplary system and exemplary system topologies.FIG. 3 illustrates an exemplary functional architecture. FIG. 4illustrates an exemplary protocol stack. FIG. 5 illustrates an exemplaryQSM. FIGS. 6-8 conceptually illustrates how tasks for one or morequeries may be processed embodiments of the present invention.

Reference may now be made in detail to the exemplary embodiments of theinvention, which are illustrated in the accompanying drawings. Whereverpossible, the same reference numbers may be used throughout the drawingsto refer to the same or like parts.

FIG. 1—An Exemplary C2 System

FIG. 1 illustrates an exemplary system 100 of the C2 solution. As shown,system 100 may comprise an application 102 that is running on a client104, such as a personal computer or other system. Application 102interfaces a set of DBMS nodes 106 a-n across a network 108, such as theInternet, local area network, etc. DBMS nodes 106 a-n may furtherinterface one or more databases stored in storage infrastructures 112a-n, respectively. For purposes of explanation, DBMS nodes 106 a-n andits components may be collectively referred to in this disclosure as anode of system 100. As shown, system 100 may comprise multiple nodes.The various components of FIG. 1 may now be further described.

Application 102 may be any computer software that requests the servicesof DBMS 106. Such applications are well known to those skilled in theart. For example, application 102 may be a web browser in which a useris submitting various search requests. Of course, application 102 may beanother system or software that is consuming the services of nodes 106a-n and submitting queries to nodes 106 a-n.

Client 104 represents the hardware and software that supports theexecution of application 102. Such clients are well known to thoseskilled in the art. For example, client 104 may be a personal computeror another server.

Nodes 106 a-n may be any set of devices, including hardware and softwarethat assist in the processing of queries by system 100. In general,nodes 106 a-n are configured to support various SQL queries onrelational databases (and thus may also be known as a RDBMS). Typicalexamples of DBMSs supported by nodes 106 a-n include Oracle, DB2,Microsoft Access, Microsoft SQL Server, PostgreSQL, and MySQL.

Network 108 represents the communication infrastructure that couplesapplication 102 and DBMS 106. For example, network 108 may be theInternet. Of course, any network, such as a local area network, widearea network, etc., may be employed by the present invention.

Storage infrastructures 112 a-n comprises the computer storage devices,such as disk arrays, tape libraries, and optical drives that serve asthe storage for the databases of system 100. Storage infrastructures 112a-n may employ various architectures, such as a storage area network,network attached storage, etc., which are known to those skilled in theart.

In some embodiments, the C2 solution stores its databases in storageinfrastructure 112 a-n in column-store format. Column-store format iswhere data is stored in columns or groups of columns. Column-storeformat is advantageous for data fetching, scanning, searching, and datacompression. The column-store format may employ fixed width columns andcolumn groups with implicit RIDs and a RID to primary key column toallow for arithmetic memory addressing and translation. This allowssystem 100 to utilize hardware processing for database processing, suchas column hopping, and to operate directly on the compressed data in thecolumns. Column store format is further described in the relatedapplications, which are incorporated by reference.

FIGS. 2A, 2B, and 2C—System Topologies

FIGS. 2A, 2B, and 2C illustrates exemplary node topologies. As shown,FIG. 2A illustrates a multi-QPM node topology. FIG. 2B shows a hybridnode that employs one or more QPMs as well as software based QSMs toassist in processing of queries. And FIG. 2C shows a node that employs aplurality of QSMs. These various topologies may be utilized to customizethe C2 solution for various sizes of databases and desired performance.In addition, these topologies are provided to illustrate that the C2solution can be easily scaled up to virtually any size of database orperformance.

First, the multi-QPM node shown in FIG. 2A may be explained, whichcomprises a single host system (or BSM) 202 and multiple QPM modules 204a-n. The node 106 b may comprise a BSM 202 and one or more QPM modules204 a-n. Collectively, host 202 and QPM modules 204 a-n may be referredto as a node or appliance. In some embodiments, BSM 202 and QPM modules204 a-n are coupled together over a known communications interface, suchas a PCIe or HyperTransport (HT) interface.

In terms of packaging, BSM 202 and QPM modules 204 a-n may be built onone or more cards or blades that are bundled together in a commonchassis or merely wired together.

The BSM 202 may comprise a general purpose CPU, such as a Xeon x86processor by the Intel Corporation, and a memory, such as a dynamicrandom access memory. Such types of host systems are well known to thoseskilled in the art. In general, in the C2 solution, BSM 202 may be usedto process parts of a query that are less time consuming (i.e., slowpath portion), such as server-client connection, authentication, SQLparsing, logging, etc. However, in order to optimize performance, thebulk of query execution (i.e., the fast path portion) is offloaded tothe QPM modules 204 a-n.

BSM 202 may run MySQL software 114 and also run C2 software 110 thatorchestrates query processing between MySQL 114 and QPM modules 204 a-n.In particular, C2 software 110 may decompose a query into a set of queryfragments. Each fragment comprises various tasks, which may have certaindependencies. C2 software 110 may determine which fragments and tasksare part of the fast path portion and offload them to one of the QPMmodule 204 a-n. Appropriate tasks for the selected query fragments aresent to QPM modules 204 a-n with information on the database operationdependency graph. Within the QPM modules 204 a-n, tasks are furtherbroken down into parallel/pipelined machine code operations (known asMOPs) and executed in hardware.

As described in the related applications, a QPM module 204 comprisesprocessing logic and a relatively large memory for hardware acceleratingdatabase operations of the node. In some embodiments, QPM module 204 isconfigured to handle various repetitive database tasks, such as tablescanning, indexing, etc. In the C2 solution, QPM modules 204 a-n canreceive high-level database query tasks (not just low-level read/writeor primitive computation tasks as is typically for a general purposeprocessor) in the form of machine code database instructions.

QPM logic is the hardware that executes machine code databaseinstructions for the database tasks being handled by a QPM module 204.To adapt to application requirement changes, the QPM logic is designedto have hardware reconfigurability. Accordingly, in some embodiments,the QPM logic is implemented using field programmable gate arrays(FPGAs). However, any type of custom integrated circuit, such asapplication specific integrated circuits (ASICs), may be implemented asQPM logic 302.

When it is implemented as a rigid ASIC, it is also possible to keep thereconfigurability of any one of QPM modules 204 a-n by embedding FPGAcores in the ASIC (i.e., a mixed implementation). The reconfigurabilityof QPM modules 204 a-n may have significance in allowing the C2 hardwareplatform to be “re-programmed” to adapt to changing application needs.

For example, a software patch or release may include a new FPGA image(s)that upgrade QPM modules 204 a-n, in a manner similar to the waysoftware or firmware can be upgraded. These new FPGA images may bedownloaded by offlining the target QPM module to: fix functional bugs;add new features for functionality or better performance; or any otherapplication/customer specific adaptation.

Multiple FPGA images could be stored in an Electrically ErasableProgrammable Read Only Memory (EEPROM) or flash memory of the FPGA. EachFPGA image may then have its own unique functionality. One image couldbe used to speed up a fast loader (bulk) operation, which is normallydone when there are no queries in the system (either parsed oroffloaded). Another image could be used or loaded if an applicationrequires a lot of text processing (structured, unstructured, or semistructured) and needs additional acceleration specific to text search,regular expressions, and other work/text related operations. Yet anotherimage could be loaded for pattern matching queries related to DNA orprotein search in bio-informatics applications. These FPGA images may beactivated one at a time depending on customer application setup ordynamically loaded based on current active application workload.

The QPM memory serves as the memory of a QPM module 204. In order tomaximize the efficiency of the QPM logic, the QPM memory 304 may beimplemented using relatively large amounts of memory. For example, insome embodiments, the QPM memory in a QPM module 204 may comprise 256Giga-Bytes or more of RAM or DRAM. Of course, even larger amounts ofmemory may be installed in QPM module 204.

In addition, in some embodiments, the QPM memory of various QPM modules204 may be aggregated into a common pool of shared memory. Suchaggregation may be accomplished based on having QPM modules 204 a-nemploy a common virtual address space.

FIG. 2B illustrates a hybrid node 106 n. As shown, node 106 may comprisea single BSM 202, multiple QPM modules 204 a-n, and one or more QSMmodules 206 a-n. In some embodiments, BSM 202, QPM modules 204 a-n, andQSM modules 206 a-n are coupled together over a known communicationsinterface, such as a PCIe or HyperTransport (HT) interface.

As can be seen, hybrid node 106 n differs from a multi-QPM node in thatcomprises QSM modules 206 a-n. As noted, QSM modules 206 a-n may besoftware-based modules that are coupled to hybrid node 106 n. Forexample, legacy servers executing queries based on typical databasesoftware may be integrated into hybrid node 106 n as a QSM. Of course,conventional servers executing DBMS software may be employed as a QSM.This architecture allows hybrid node 106 n to handle various types ofqueries that are better suited for software rather than hardwareacceleration.

FIG. 2C illustrates a multi-QSM node. As shown, the node may comprise asingle BSM and multiple QSM modules. In some embodiments, these modulesare coupled together over a known communications interface, such as aPCIe or HyperTransport (HT) interface.

As noted, QSM modules may be software-based modules that are coupled tothe BSM. As will be recognized by those skilled in the art, any of theQSMs may be executing on the same underlying hardware platform or may beexecuting on different hardware platforms and coupled together via acommunications interface. This architecture allows the multi-QSM node tohandle various types of queries that are better suited for softwarerather than hardware acceleration while employing a clustered approachto these queries or tasks.

FIG. 3—C2 Software Architecture

As noted, C2 software 110 orchestrates the processing a query betweenMySQL software 114 and QPM module 204. In some embodiments, C2 software110 runs as an application on BSM 202 and as a storage engine of MySQLsoftware 114. As shown in FIG. 3, the QSM manager shares a control pathwith other QSMs. In addition, the QSM manager may share data with otherQPMs, for example, via the crossbar with the other QPMs. For example, asfurther illustrated in FIG. 3A, data paths, e.g., shared flow, can occuramong all the QSMs and QPMs. The control path used to send instructionsand commands may utilize the same network of connections as the datapaths. However, the control paths to a QSM or QPM may be independent orseparate from the data path. (FIG. 4 also illustrates an architecture ofthe C2 software 110.) As shown, C2 software 110 comprises a query andplan manager 402, a query reduction/rewrite module 404, an optimizer406, a post optimizer rewrite module 408, a query plan generator 410, aquery execution engine (QEE) 412, a buffer manager 414, a task manager416, a memory manager 418, a storage manager 420, an answer manager 422,an update manager 424, shared utilities 426, a QPM manager 428, and aQSM manager 430.

As also shown, BSM 202 is shown coupled to one or more QPM modules 204a-n and one or more CSM modules 206 a-n. Each of the components of BSM202 may now be briefly described.

Query and plan manager 402 analyzes and represents the parsed queryreceived from the MySQL software 114, annotates the query, and providesan annotation graph representation of the query plan. Queryreduction/rewrite module 404 breaks the query into query fragments andrewrites the query fragments into tasks. Rewrites may be needed forcompressed domain rewrites and machine code database instructionoperator rewrites. Optimizer 406 performs cost-based optimization to bedone using cost model of resources available to C2 software 110, i.e.,QPM module 204, resources of C2 software 110 itself using softwareoperations, or MySQL software 114.

These modules interact with each other to determine how to execute aquery, such as a SQL query from MySQL software 114. The data structuresoutput by the query plan generator 410 may be the same data structurethat the optimizer 406 and the rewrite module 404 may operate on. Once aparsed SQL query has been represented in this data structure (converted,for example, from MySQL), query and plan manager 402 rewrites the querysuch that each fragment of the query can be done entirely in MySQLsoftware 114, in C2 software 110, or in QPM module 204. Once the finalquery representation is available, the rewrite module 404 goes throughand breaks the graph into query fragments.

Post optimizer module 408 is an optional component that rewrites afterthe optimizer 406 for coalescing improvements found by optimizer 406.Query plan generator 410 generates an annotations-based, template-drivenplan generation for the query tasks.

QEE 412 executes the query fragments that are to be handled by softwareor supervises the query execution in QPM module 204 via QPM manager 428.For example, the execution engine 412 handles faults from QPM.Transitions MOPs to SOPs if MOPs fail. Of note, one skilled in the artwill recognize that this is but one approach. QSMs may be added in ahierarchical fashion, for example. In some embodiments, a QSM manager isprovided within the host system or BSM 202 to manage a QSM. Of course, aQSM manager may be implemented as software running on a BSM.Accordingly, QSM can be remote from QEE 412. A MOP-SOP hybrid plan maybe utilized when a query has: SQL functions; requires arithmeticprecision; requires divide and arithmetic operations >64 bits; requiresa large Order By limit. In some embodiments, a SOP task supersedes a MOPtask within a originally formed DAG of tasks to optimize the SOP-MOPcommunication

Buffer manager 414 manages the buffers of data held in the memory ofhost 202 and for the software execution tasks handled by host 202. Taskmanager 416 orchestrates the execution of all the tasks in QPM module204 and C2 software execution engine 412.

Memory manager 416 manages the virtual address and physical addressspace employed by C2 software 110 and QPM module 204 in QPM memory 304.In some embodiments, memory manager 416 utilizes a 50 bit VA addressing(i.e., in excess of 1 petabyte). This allows C2 software 110 to globallyaddress an entire database and optimize hardware execution of the querytasks.

Storage manager 420 is responsible for managing transfers of data fromQPM memory 304 to/from storage infrastructure 112. Answer manager 422 isresponsible for compiling the results of the query fragments andproviding the result to MySQL software 114 via the API 116.

Update manager 424 is responsible for updating any data in the databasestored in storage infrastructure 112. Shared utilities 426 providevarious utilities for the components of C2 software 110. For example,these shared utilities may include a performance monitor, a metadatamanager, an exception handler, a compression library, a logging andrecovery manager, and a bulk/incremental loader.

In some embodiments, update manager 424 supports snapshot versioning isto support Query-While-Load operations. In snapshot versioning,logically each row has creation/deletion timestamp. This timestamp canbe represented as using a snapshot version number (“svn”). An “UPDATE”operation is atomically converted into DELETE followed by INSERT. Thecreation timestamp can now be replaced by watermark per svn. “VALID”bitmap vectors are then created for each table, for example, CSV0 . . .. CSVn (Committed Snapshot Vector at svn=0 . . . n), indicates validrows at the specific svn #. One CSV is specified per each active svn #and reclaimed when SVN is no longer in use.

An Uncommitted Latest Vector for m open transactions (ULV0 . . . ULVm)may be used to indicate valid rows of uncommitted data that is in themiddle of open transaction. In order to transition ULV to latest CSVx,the CSVx eventually becomes CSVn at snapshot refresh/creation.

For a snapshot refresh, update manager 424 may convert a Change Log intoa set of IUD list, where the array entry is: Table, Col, RID#, Value.Update manager 424 may also employ a scattered write MOP to allow fastrandom write when applying mixed IUD list. The following figuresillustrate variations of snapshot versioning supported by theembodiments.

QPM manager 428 controls execution of the tasks in QPM module 204 bysetting up the machine code database instructions and handles allinterrupts from any of the hardware in QPM module 204. In someembodiments, QPM manager 428 employs a function library known as aHardware Acceleration Function Library (HAFL) in order to make itsfunction calls to QPM module 204.

QSM manager 430 controls execution of the tasks in a CSM module 206 byinterfacing with the software executing on CSM module 206. In someembodiments, CSM manager 430 employs an API or library in order to makeits function calls to CSM module 206.

FIG. 4—System Software Stack of C2 Software and Hardware

As shown, a SQL query is received in the RDBMS layer, i.e., MySQLsoftware 114. MySQL software 114 then passes the SQL query via API 116to C2 software 110. In C2 software 110, the SQL query is processed. Atthis layer, C2 software 110 also manages retrieving data for the SQLquery, if necessary, from storage infrastructure 112, from BSM 202, orfrom CSM modules 206 a-n.

In order to communicate with QPM modules 204 a-n, QPM manager 428employs the HAFL layer in order to make its function calls to QPMmodules 204 a-n. In order to allow for variances in hardware that mayexist in QPM module 204, the system software stack may also comprise ahardware abstraction layer (HAL). Information is then passed from C2software 110 to QPM modules 204 a-n in the form of machine code databaseinstructions via an interconnect layer. As noted, this interconnectlayer may be in accordance with the well-known PCIe or HT standards.

Within QPM modules 204 a-n, the machine code database instructions areparsed and forwarded to its QPM logic. These instructions may relate toa variety of tasks and operations. For example, as shown, the systemsoftware stack provides for systems management, task coordination, anddirect memory access to QPM memory.

As shown, in the QPM logic, machine code database instructions areinterpreted for the various types of processing elements (PE). QPM logicmay interface with QPM memory, i.e., direct memory access by utilizingthe memory management layer. Further details about QPM logic may befound in the related applications, which are incorporated by reference.

FIG. 5—Computer Software Module

Referring now to FIG. 5, an exemplary Query Software Module (QSM) isshown that is consistent with the principles of the present invention.As noted, a QSM may comprise a general purpose CPU, such as a Xeon x86processor by the Intel Corporation, and a memory, such as a dynamicrandom access memory. Such types of software-based systems or serversare well known to those skilled in the art. In general, in the C2solution, a QSM may be used to process parts of a query (accomplished bySOPs) that are more time consuming (i.e., slow path portion) or morecomplex in nature, such as server-client connection, authentication, SQLparsing, logging, etc. However, in order to optimize performance, a QSMmay operate in conjunction with QPM modules 204 a-n.

For example, in some embodiments, a QSM may run MySQL software andinterface C2 software 110 in the BSM 202. In particular, C2 software 110may decompose a query into a set of query fragments. Each fragmentcomprises various tasks, which may have certain dependencies. C2software 110 may determine which fragments and tasks offloaded to one ofthe QPM module 204 a-n or the QSM. Appropriate tasks for the selectedquery fragments are sent to the QSM with information on the databaseoperation dependency graph. Within the QSM, tasks may be further brokendown into operations. Since these operations are executed with softwarepredominantly, the present disclosure may refer to them as SOPs.

FIG. 7—MOP Parallelism

FIG. 7 illustrates a typical MOP plan and how it may be partitioned andmapped to multiple QPMs for parallel execution. Each node in the shownMOP plan represents a MOP, or a sub-graph of multiple MOPs. Eachdirected edge of the graph represents at least one flow of executiondata from the producer node to at least one consumer node. Without aloss of generality, one skilled in the art will understand that a “node”may be a MOP, a collection of MOPs, a subtree of the DAG, and the like.

As shown, the MOP plan has been split into tasks: T0, T1, . . . T4, eachof which may be optimized to meet the execution resource limitations ofa single QPM. This enables fully parallel execution when scheduled torun simultaneously across multiple QPMs.

The QPMs may be connected via a network interconnect allowing any QPM toshare its execution flows with at least one other QPM, in a dynamicallyconfigurable manner. This feature facilitates the flow transfer of theoriginal MOP plan, for example, between tasks T0 and T1, T0 and T2, T1and T4, and so on.

In some embodiments, as a matter of QPM storage efficiency, a given MOPtask may be data partitioned to execute what is effectively the sameoperations on distinct portions of at least one large data source. Thisform of partitioning is illustrated, for example, where task T0 is“replicated” as T(0,0), T(0,1), and T(0,2), across QPMs: Q0, Q1, and Q2,respectively. This partitioning may be performed in order to fulfill theexecution requirements of T0 across distinct portions of data (e.g, aSPMF).

Lastly, FIG. 7 illustrates pipelined parallelism in executingsimultaneously across multiple QPMs. In such parallelism, the nodes maycooperate according to producer-consumer relationships established bythe data flows. That is, each consumer node receives and processes itsinput(s) as the data arrives from its associated producer node(s). Thischaining forms a pipeline, which can maintain a high executionthroughput rate. This feature is exemplified in FIG. 7, for example, bythe data flows from QPMs Q0, Q1, and Q2 to QPM Qi, where T1 executes,and to QPM Qj, where T2 executes, which have been established both tofulfill the (functional) MOP plan data flow requirements, and toestablish a pipeline through which producers and consumers may worksimultaneously.

In one embodiment, the present invention is integrated with existing,conventional row-store technologies. The figure below illustrates a highlevel block diagram of an exemplary integrated system stack. The bluecomponents represent a conventional row-store RDBMS software and primarydatabase storage. The green components are QSM software (physical stack)and hardware components (QPM and SSD) that sit underneath the RDBMSlayer. From users and application point of view, this integrated stackis transparent and backward compatible.

To simplify the design, the embodiments of the present invention mayutilize cached data while the RDBMS continues to own the primary data.The cached data is organized in compressed column-store format to takefull performance advantage of the QPM and dataflow execution. The cacheddata could be the current hot and warm columns of active database(s).Cold columns can usually reside in disk storage of the RDBMS.

Row store-to-column store format conversion may be accelerated using aMOP that is augmented with “RS-to-CS transpose” capability to supportthis acceleration. The column-store cache can behave like a real cacheto the RDBMS row-store database. It is able to dynamically hold currentactive workload (hot+warm columns) at a given period.

One or more of the QPMs may maintain information on which columns arepresent in the cache. If necessary, the RDBMS can access thisinformation to determine which Query Fragments (QFs) to accelerate inthe QPMs. This does not preclude any of the QPMs to execute againstcolumns that do not currently exist in the cache. The embodiments alsohave flexibility in the design to determine which columns to cache. Forexample, the RDBMS can give hints to QPMs on which columns need to becached. The QPMs can learn from RDBMS schema/metadata to determine whichcolumns are likely hot/warm (e.g., DATE columns).

QPMs may learn dynamically overtime on which columns are used frequently(hot) or semi-frequently (warm) based on the QFs sent. For example, a“column usage count” can be used in the QPM cache victimizationalgorithm. So as long as a column is of the data type that a QPM canconvert and execute, a QF's workset columns may have cold column(s) thatthe QPM may fetch on demand into the CS cache. In some embodiments, theCS cache content may dynamically adjust as the active workload shiftsovertime.

Typically, the CS cache may be first populated when the RDBMS's RSdatabase is initially bulk loaded. As there are incremental bulk loads,the QPM CS may also be updated by calling the appropriate API functions.On DML trickle updates and crash recovery, the RDBMS may automaticallymaintain CS cache coherency by logging all committed transactions (notrollbacked) into an asynchronous change list (ACL), which aresubsequently updated into CS cache.

During QF execution, the QPMs may take care to fetch the missing columnsinto CS cache. With pipelined execution, QPMs may bring chunks (RIDranges) of the missing column to hide some of the I/O latency. Given thehardware accelerated RS-to-CS conversion, the conversion bandwidth isestimated to be better than disk I/O bandwidth, thus not becoming thebottleneck.

To improve CS cache efficiency, the RDBMS can tag a QF column as “do notcache” as they are of intermediate result or “forever cold” column type.In summary, a QPM can always execute any QF irrespective of currentcache content, but, by providing proper cache hints, performance can bevery efficient.

FIG. 16 illustrates a RDBMS node accelerated based on the embodiments,which includes one or more QPMs and a SSD array. These hardwarecomponents may connect through standard PCI Express (PCIe) interface.Each QPM may comprise a dataflow SQL chip and a memory (such as 256 GB).Multiple processors may share the same accelerator.

To interface, a software library “PhyStack” runs as a set of threadswith callable functions. The internals of this physical stack aredescribed further below.

The PhyStack comprises four primary components: query fragment (QF)processing, load/update/transaction processing, platform operatingsystem, and firmware. On the query processing, the RDBMS sends a QF tobe executed in the accelerator based on query operation cost and types.A QF contains one or more SQL operations, structured in a dataflowgraph. The second component handles bulk loading (initial load,incremental insert/delete/update), trickle updates from DML, andtransaction-related operations.

The third component handles operating system functions, such asquery/prefetch task scheduling, QPM memory management, SSD storagemanagement, and data transfer manager to/from QPM memory. The fourthcomponent provides hardware abstraction, DMA/PCIe drivers, interrupthandler, and diagnostics of QPM.

In some embodiments, there are different options of integrating queryexecution into the RDBMS. For example, the RDBMS optimizer can be madeaware of the QPM and the associated cost models. This can be done in amodular way such that the optimizer is not to be exposed to the internaldetails of the QPM execution. The QPM cost models can be exposed to theoptimizer allowing it to cost the operations in its regular joinenumeration algorithm.

In general, a SQL query statement goes through parsing and logicalrewrite/optimization steps in the RDBMS layer. The query plan is thenexecuted in multiple virtual processors, which subsequently pass thequery plan to be executed in the accelerator, i.e., using a QPM or QSM.In a conventional RDBMS implementation, this query plan is a singleoperation (e.g., scan, join, etc.) at a time.

Ideally, for efficiency, this query plan would consist of moreoperations in the QF dataflow graph. The QF API should allow specifyingall the dataflow operators and building the dataflow graph by specifyingthe connections in the graph. The API can be defined in various ways,from passing the parsed (and annotated) query tree object directly,using dataflow query language to using a C++ programmatic interface.

The following examples show the proposed QF APIs for submitting a QF andfor sending QF results. In addition, there may be API calls defined forthe RDBMS optimizer to get some cost functions.

// Base class to create expressions class kf_expression {  Vector<kf_expression*> m_vChildExprs; }; // Subclasses of Expressionclass to specify different expressions class kf_arithExpression: publickf_expression { }; class kf_compExpression: public kf_expression{ }; . .. // Base class to specify a dataflow operator class kf_dataflow_op {  setInputFlow(kf_dataflow_op* inputDF);  setProjections(vector<kf_expression*>& projectionExprs);  Vector<kf_dataflow_op*> m_vInputOps; }; // Subclasses to specifydifferent dataflow operations class kf_dataflow_scan: publickf_dataflow_op {   kf_dataflow_scan(string table_name);  setInputTable(string table_name); }; class kf_dataflow_filter: publickf_dataflow_op {   setFilterExpression( kf_compExpression* filterExpr);}; class kf_dataflow_groupby: public kf_dataflow_op {  setGroupByKey(vector<kf_expression*>& gbKeyExprs);  setAggregateExprs(vector<kf_expression*>& aggExprs); }; . . .Usage ExampleSQL Query:

SELECT   p_container,   sum(l_extendedprice*(1−l_discount)) AS revenueFROM   lineitem,   part WHERE   l_partkey = p_partkey AND   p_sizebetween 1 and 5 AND   p_brand = ‘Brand#12’ GROUP BY   p_containerLogical DataFlow:r1=FILTER part BY (p_size between 1 and 5)r2=FILTER r1 BY (p_brand=‘Brand#12’)r3=JOIN lineitem BY l_partkey, r2 by p_partkeyr4=GROUP r3 BY p_containerr5=FOREACH r4 GENERATE p_container, SUM(l_extendedprice*(1−l_discount))Dataflow Programmed Using the API:

kf_dataflow_scan *r0 = new kf_dataflow_scan(“part”); // Constructexpression for p_size between 1 and 5 kf_compExpression *psizeEx = newkf_compExpression( ); kf_dataflow_filter *r1 = new kf_dataflowfilter( );r1->setInputFlow(r0); r1->setFilterExpression(psizeEx); // Constructexpression for p_brand = ‘Brand#12’ kf_compExpression *pbrandEx = newkf_compExpression( ); kf_dataflow_filter *r2 = new kf_dataflow_filter(); r2->setInputFlow(r1); r2->setFilterExpression(pbrandEx); //Constructexpression for join with lineitem. kf_compExpression *pjoinEx = newkf_compExpression( ); kf_dataflow_join *r3 = new kf_dataflow_join( );r3->setInputFlow(r2); r3->setInputTable(“lineitem”);r3->setJoinExpression(pjoinEx); // Similarly program the remainingoperators kf_dataflow_groupby *r4 = new kf_dataflow_groupby( );r4->setInputFlow(r3); r4->setGroupbyKey( ); r4->setAggregateExprs( );

This QF is given to the accelerator to execute and eventually producethe QF result set.

Sending Results

There are two ways a virtual processor is able to obtain results fromthe accelerator. The results can be pulled by a processor or pushed tothe processor by invoking a callback. A callback may be ageneral-purpose abstraction for result retrieval.

// Base class that holds the values for all datatypes. Class kf_field {}; Class kf_field_integer: public kf_field { }; Class kf_field_decimal:public kf_field { }; Class kf_field_string: public kf_field { }; Classkf_resultSet {   send_result_row( ); };

During initialization/startup, the RDBMS may pass initial DDLinformation to accelerator for all tables and column info to be cached.The RDBMS can invoke a DDL APIs when tables are created, dropped ormodified. DDL drivers for the accelerator may set up The acceleratorMetadata appropriately.

DDL drivers are passed one of the above descriptors along with some highlevel description of the operation. The accelerator may provide some DDLlevel wrapper APIs that may be able to take inputs from the RDBMS andinternally form the descriptors and execute the DDL operations.

As users issue bulk loading commands (initial or incremental) to theRDBMS system, the bulk loading is done first at the RDBMS RS side, andsubsequently updated to the CS cache side in a pipelined fashion. Toreduce the x86 CPU and memory resource contention, CS cache loadexecution can be mostly offloaded/accelerated in QPM.

Below are some examples of bulk load API functions:

kf_load_handle kf_load_begin(load_type, table,       [ input_desc ]+,       [ col_binding ]+,       ...// other misc flags etc       );ret_code_t kf_load_next(load_handle,           [ (buffer, size) ]+ ); // one (buffer, size) tuple for each input_desc.  // zero or more _nextcalls to send data to kfdb. ret_code_t kf_load_end( load_handle,end_type ); load_type: { INITIAL, BULK_INSERT, BULK_UPDATE, BULK_DELETE,BULK_POPULATE }.   BULK_POPULATE: is to re-populate for data that hadbeen   evicted from CS (e.g., base column was evicted but not itsrelated   hot index column, or a new column RID range of existing columnis   added to CS cache) end_type: { SUCCESS, ABORT } input_desc: eachdescribes the format of a row major buffer to be passed   in. A specialinput_desc can be used to for a buffer providing   ranges of RIDs, whichmay be applicable with BULK_DELETE,   BULK_UPDATE, or BULK_POPULATE.col_binding: each specifies one column of the table to be loaded plusits “position” in the input_desc's.   BULK_DELETE/BULK_UPDATE wouldexpect a special column   binding corresponding to the RIDs to delete orupdate.

Trickle updates from DML operation (mix of insert/update/delete) areusually non-batchable, unlike the bulk loading operation. As thesetrickle updates are committed and logged into RDBMS's recovery/changelog, it may call an API to update the accelerator's Asynchronous ChangeLog (ACL), which is persisted in SSD. The accelerator may filter the ACLto gradually apply only appropriate cached column data into CS cache. Ifsubsequent QF is submitted which requires updated data from the ACL, thecorresponding change(s) may be applied prior to execution.

For transaction processing, the RDBMS coordinates a commit to the ACLbefore concluding its commit. In this way, the accelerator can beoblivious to uncommitted transactions (rollbacks). Given that a CS cacheonly consists of committed data, queries involving uncommitted data needto be executed on the RDBMS side.

With respect to concurrent operations, the RDBMS can be used to resolveall read/write locks prior to issuing a QF to the accelerator.Therefore, the accelerator can assume all issued operations can beexecuted concurrently once the ACL dependency has been resolved.

The CS cache of accelerator is generally in sync transactionally. Thecache content may not be transactionally latest since it still needs tobe merged with entries in the ACL. Between the ACL and the cache, theaccelerator may contain all committed data. Any queries executed mayreflect all changes in the ACL. All dependent ACL updates prior to aQF's timepoint (the RDBMS arrival timestamp) are pushed to CS Cacheprior to the QF execution. Queries that don't depend on outstanding ACLentries can be executed immediately. Each ACL entry has timestamprelative to RDBMS side. In general, the ACL is drained quickly so thereis no impact to QF execution.

During crash recovery, the RDBMS may go through its recovery processindependently and update the accelerator on the resolution of any“in-doubt” transactions. The accelerator may subsequently adjust its ACLappropriately. Eventually all committed transactions in this ACL may beapplied to the CS cache. The accelerator does not need to directlyparticipate in the RDBMS's UNDO/REDO recovery process.

Within a RDBMS node, both the RDBMS and the accelerator software stacksrun on the same x86 server. The accelerator software may employ a bulkmemory pool allocation/deallocation. This feature can be interfaced withRDBMS's memory management API.

As noted, the accelerator can efficiently support the transposition ofrow store data into its native column store form. This operation lendsitself naturally to a sequence of pipelined-parallel processes and isthus dataflow friendly, which makes it a good candidate for QPMprocessing support. Assuming some hardware (MOP) enhancements, the QPMcan be utilized to offload the transposition operation, thusaccelerating the availability of new data.

As illustrated, the transposition flow begins with the process ofidentification, which involves locating the columns of interest andtheir boundaries within each row record. The accelerator's softwarestack can be used to perform this step to parse row store structureefficiently. Later, QPM can be leveraged to further improve performanceof this step and reduce the load on the x86 processor.

Once columns have been located, slicing is the process of extricatingand separating the columns into discrete flows. Each column slice flowsin pipelined fashion, independent of other columns, where pipeline orderretains correlation with the input row order.

Compression is then applied over each column slice, according to indexlookups and arithmetic transformations appropriate for each individualcolumn. The optimal type of compression would already have beendetermined. Lastly, the process of Packing coalesces the elements ofeach independent column pipeline together into the accelerator's densecolumn store format.

If the load is incremental, the column data (column fragment) is writteninto an existing column. If the load is uncommitted, the column fragmentcan be stored separately and combined with the committed column on thefly. As needed, committed columns can be sent to SSD.

With only incremental enhancements, this flow can be implemented usingexisting QPM MOPs. In general, the transposition operation is expectedto be very efficient.

In some embodiment, the accelerator system stack can be managed as asub-component in the RDBMS RAS architecture (like local RAID or servermemory management). The accelerator RAS manager may report RAS events tothe RDBMS RAS manager. Responsibility for responding to QPM events wouldexist with the accelerator RAS manager to enable rapid recovery of localevents. General system server management responsibility would remainwith the RDBMS RAS manager.

A highly reliable and serviceable system may be maintained through thefollowing features:

ECC protected QPM DIMM

Robust PCIe communications

Protected internal QPM and SQL Chip communications

RAID configured SSD (if needed)

N+1 Hot swappable fans

N+1 Hot swappable PSUs (power supply units) in PSM (power supply module)

Active QPM/PSM monitoring (temperature, fans, voltage, data integrity, .. . )

Fault tolerant DIMM system (if needed, mask and continue)

In some embodiments, RDBMS (TD) data changes from DMLs to are synced tothe accelerator (KF) CS cache. First, ACL entry, called Data ChangeRecord, is discussed. Then, the different operations involving the ACLto keep the cache and QF coherent are discussed.

The data change records describe the changes that are made by DMLs tothe TD database. The following is an exemplary format of a DCR:

TransactionId, Operation, [Key/RID], [Data]

TransactionId: The unique id of the transaction. This can be the same asthe TD txn id. This may be used for correlating all changes belonging toa transaction.

-   -   Operation: Describes the type of DML: Insert/Update/Delete        -   Insert:        -   key/RID is not specified. Data contains an array of values.            Column Id is not specified.        -   Update:        -   Data specifies the Column Id and the corresponding data to            update        -   Delete:        -   Data is not specified. Key/RID uniquely identifies the row            to be deleted.        -   In addition to DML operations, Operation field can be Commit            or Rollback.    -   Key: Single or composite key. The key is used to identify the        exact row being updated. Key can be replaced by RID if a        universal RID numbering is consistent across TD RS and KF CS        side.    -   Data: Column Id and its value

An Asynchronous change log is a persistent staging area that is used fortemporarily holding the data change records generated. A Sync thread isa background thread that reads DCRs and syncs the changes onto KF CSCache. However, data from ACL can be synced on demand when a QF issubmitted for execution. The accelerator's internal locks are held by KFSync thread when data changes are written to the KF cache. This processis similar to a DML being performed on the accelerator database.

In some embodiments, only committed transactions are synced to thecache. While processing the DCRs, if a rollback record is encountered,all DCRs corresponding to the transaction are skipped. DCRs can begenerated from the following sources within TD:

-   -   DMLs—DCRs are written to ACL in same txn as that of the DML        (synchronous). This approach is simpler to implement and may be        discussed in this document.    -   Transaction Logs (not discussed as a part of this doc)—DCRs are        mined from the transaction log and processed asynchronously

In the first phase, there may be one Sync thread, but the system can beenhanced to have multiple Sync threads syncing changes from ACL inparallel after dependencies are resolved.

DCRs are generated while the DML is in progress after the keys (or RIDs)have been resolved. DCRs are written to ACL as a part of the DMLoperation on TD. There are multiple options to implement it. Forexample, DCRs can be written to ACL as and when they are formed duringDML execution. A Commit or rollback record is written to the ACL when TDtransaction commits or rolls back respectively. This may result in DCRsof different transactions being interleaved in the ACL.

DCRs can be buffered for the entire transaction and written to ACL onlywhen the transaction is committed. For rolled back transactions, thebuffered DCRs can be discarded. Alternatively, a hybrid approach may beto buffer certain number of DCRs and then write them all to ACL. Acommit or rollback record is written when TD transaction commits orrollsback respectively.

A TD may write DCRs for the hot and warm columns. Cold column changesare not tracked by KF CS Cache. Filtering of cold column DCRs can beeither performed by TD during DMLs or by Sync thread during syncing.

When a QF is submitted to the cache, all DCRs for the table(s) affectedby the QF may need to be synched before the QF can be executed on KF CScache. Logically, this is done by synching all DCRs that have committedbefore the QF is submitted, as determined by the DML/QF's timepoint.

Unrelated tables can continue to be synched by a Sync threadconcurrently while a query fragment is being executed. For example, ifthe ACL contains the following DCRs for tables A, B, C and D:

DCR A1 (table A) Txn 1

DCR A2 (table A) Txn 1

Commit Txn 1

DCR B2 (table B) Txn 2

Commit Txn 2

DCR C2 (table C) Txn 3

Rollback Txn 3

DCR D1 (table D) Txn 4

DCR D2 (table D) Txn 4

Commit Txn 4

DCR A1 arrived first and DCR D2 arrived last. Now, a QF that depends ontable B is received. At this time, all DCRs up to Txn 2 are required toby synced before the query can be executed. Opportunistically DCRs fromTxn 3 onwards can be synced by the Sync thread because they don'tconflict with outstanding QFs. Note that transaction 3 may be skipped byKF Sync thread as it's a “rolled back transaction”. While not shown inthe example above, DCRs of different transactions may be interleaved inthe ACL due to concurrent execution.

The accelerator can maintain a ‘temperature’ of the columns based on theusage pattern. Hot and warm columns are kept in the KF CS Cache(QPM+SSD). Columns that are present in the KF CS Cache are syncedthrough the ACL. Cold columns can be read from TD on demand duringprefetching phase of KF query execution. Data from TD row store istransformed into columnar data and brought into KF cache on demand. Inthe embodiments, there are different options for integration as thequery moves down the stack of the database kernel. For example, theseoptions may include: an unoptimized Parse Tree; an optimized Query Graph(called operation tree or White Tree in RDBMS documentation); and aQuery Execution layer

For an unoptimized query graph, the accelerator takes an all-or-nothingapproach with query processing where the MySQL parse tree is inspectedto see whether all operations can be done in the accelerator. If so, theparse tree is translated into the accelerator's internal query graphrepresentation using a bridge module.

As an extension of this all or nothing approach, the embodiments mayhave an implementation by which a query is broken into fragments each ofwhich is executed either in the accelerator or MySQL. Data is exchangedbetween the two using temporary relations to store results from thequery fragments.

For an optimized query graph, instead of integrating at the parse treelevel, integration can be performed by a post optimizer (joinenumeration). This has the advantage of being able to make a cost baseddecision of what fragments of the query should be executed.

This can be implemented in the optimizer code and the optimizer needs tobe aware of the QPM and the associated cost models. One way to do thisin a modular way is for the optimizer not to be exposed to the internaldetails of the QPM execution (which is left up to the acceleratoroptimizer). New operations may be modeled in addition to the existingaccess and join methods that the RDBMS already has. The QPM cost modelscan be exposed to the optimizer allowing it to cost the operations inits regular join enumeration algorithm.

Another option is to delay the integration point to later in the queryprocessing so that the compiler mostly stays out of the picture and theintegration is done at query execution layer by dispatching certainoperations to the QPM.

For example, the White tree may be converted into an EVL tree, which canbe interpreted or translated further into machine code. A layertranslates for the EVL (or machine) code to QPM execution plan, which isa dataflow graph of MOPs and SOPs.

In this approach, the accelerator specific rewrites are leveraged toallow the computation to happen in compressed domain. Another rewritelayer may be added to perform these operations on the MOP-SOP graphdirectly instead of the query graph layer.

FIG. 18 shows an example of hardware partitioning by an embodiment ofthe present invention. In particular, static and dynamic partitioning ofbase table columns and indexes across QPMs/CSMs may be built using MOPs.For example, given M input key/value pairs (K_(i), V_(i)), a MOP may mapeach key instance K_(i) into a partition P_(j) of a possible Npartitions. While doing so, each K may be assigned a unique sequentialoffset C_(jk) within its partition. In order to build the partition, acumulative distribution point A_(j) for each partition P_(j) may becomputed by the MOP. If there is an excessive skew, then the MOP mayrefer this operation to a SOP or other software. The original keyinstance or reference is then mapped into the partition space beingbuilt, such that each reference is at its unique Cjk from its partitionaddress Aj.

Referring now to FIG. 19, once the partition is built it may be consumedby a MOP or SOP. For example, as shown, information in the partition maybe consumed by MOPS for aggregation, sorting, a hash join, and the like.FIG. 20 shows an example of a hardware hash join according to anembodiment of the present invention.

Other embodiments of the invention may be apparent to those skilled inthe art from consideration of the specification and practice of theinvention disclosed herein. It is intended that the specification andexamples be considered as exemplary only.

What is claimed is:
 1. A method for integrating relational databaseoperations through a hardware accelerator coupled with a host systemrunning a relational database management system for a relationaldatabase, said method comprising: receiving a relational query havingone or more tasks related to at least one row of the relationaldatabase; compiling the tasks into a program of database machine codeoperations specifying a stream of operations for the tasks; executingthe program of database machine code operations in a dataflowarchitecture query processing module implemented using said hardwareaccelerator, wherein the dataflow architecture query processing moduleincludes at least one node configured to receive and process its inputas data configured to arrive from at least one other node associatedwith the at least one node, and wherein said database machine codeoperations are executed within said hardware accelerator withoutsoftware; and providing a result for the tasks based on the program ofdatabase machine code operations.
 2. The method of claim 1, wherein theprogram of database machine code operations are configured to convertrows of data into a column store format.
 3. The method of claim 1,wherein the program of database machine code instructions are configuredfor compression of data from the relational database.
 4. The method ofclaim 1, wherein the program of database machine code instructions areconfigured for decompression of data from the relational database. 5.The method of claim 1, wherein the program of database machine codeinstructions are configured for encryption of data from the relationaldatabase.
 6. The method of claim 1, wherein the program of databasemachine code instructions are configured for a projection operation. 7.The method of claim 1, wherein the program of database machine codeinstructions are configured for a predicate filtering operation.
 8. Themethod of claim 1, wherein the program of database machine codeinstructions are configured for a groupby aggregation.
 9. The method ofclaim 1, wherein the program of database machine code instructions areconfigured for an orderby limit operation.
 10. The method of claim 1,wherein the program of database machine code instructions are configuredfor a hash join operation.
 11. The method of claim 1, wherein theprogram of database machine code instructions are configured for asorted merge join operation.
 12. The method of claim 1, wherein theprogram of database machine code instructions are configured for a mergesort operation.
 13. A method for integrating relational databaseoperations through a hardware accelerator coupled with a host systemrunning a relational database management system for a relationaldatabase, said method comprising: receiving a relational query havingone or more tasks related to at least one row of the relationaldatabase; compiling the tasks into a program of database machine codeoperations specifying a stream of operations for the tasks; executingthe program of database machine code operations in a dataflowarchitecture query processing module implemented using said hardwareaccelerator, wherein the program of database machine code operations areconfigured to convert rows of data into a column store format; andproviding a result for the tasks based on the program of databasemachine code operations.
 14. The method of claim 13 wherein the programof database machine code instructions are configured for compression ofdata from the relational database.
 15. A computing device that includes:a hardware accelerator; and one or more processors configured to:integrate relational database operations through the hardwareaccelerator when coupled with a host system running a relationaldatabase management system for a relational database; receive arelational query having one or more tasks related to at least one row ofthe relational database; compile the tasks into a program of databasemachine code operations specifying a stream of operations for the tasks;execute the program of database machine code operations in a dataflowarchitecture query processing module implemented using said hardwareaccelerator, wherein the dataflow architecture query processing moduleincludes at least one node configured to receive and process its inputas data configured to arrive from at least one other node associatedwith the at least one node, and wherein said database machine codeoperations are executed within said hardware accelerator withoutsoftware; and provide a result for the tasks based on the program ofdatabase machine code operations.
 16. The computing device of claim 15,wherein the program of database machine code operations are configuredto convert rows of data into a column store format.
 17. The computingdevice of claim 15, wherein the program of database machine codeinstructions are configured for compression of data from the relationaldatabase.
 18. The computing device of claim 15, wherein the program ofdatabase machine code instructions are configured for decompression ofdata from the relational database.
 19. The computing device of claim 15,wherein the program of database machine code instructions are configuredfor encryption of data from the relational database.
 20. The computingdevice of claim 15, wherein the program of database machine codeinstructions are configured for a projection operation.
 21. Thecomputing device of claim 15, wherein the program of database machinecode instructions are configured for a predicate filtering operation.22. The computing device of claim 15, wherein the program of databasemachine code instructions are configured for a groupby aggregation. 23.The computing device of claim 15, wherein the program of databasemachine code instructions are configured for an orderby limit operation.24. The computing device of claim 15, wherein the program of databasemachine code instructions are configured for a hash join operation. 25.The computing device of claim 15, wherein the program of databasemachine code instructions are configured for a sorted merge joinoperation.
 26. The computing device of claim 15, wherein the program ofdatabase machine code instructions are configured for a merge sortoperation.
 27. A non-transitory computer readable storage medium storingat least executable code for integrating relational database operationsthrough a hardware accelerator coupled with a host system running arelational database management system for a relational database, whereinwhen executed the executable code causes: receiving a relational queryhaving one or more tasks related to at least one row of the relationaldatabase; compiling the tasks into a program of database machine codeoperations specifying a stream of operations for the tasks; executingthe program of database machine code operations in a dataflowarchitecture query processing module implemented using said hardwareaccelerator, wherein the dataflow architecture query processing moduleincludes at least one node configured to receive and process its inputas data configured to arrive from at least one other node associatedwith the at least one node, and wherein said database machine codeoperations are executed within said hardware accelerator withoutsoftware; and providing a result for the tasks based on the program ofdatabase machine code operations.
 28. The non-transitory computerreadable storage medium of claim 27, wherein the program of databasemachine code operations are configured to convert rows of data into acolumn store format.
 29. The non-transitory computer readable storagemedium of claim 27, wherein the program of database machine codeinstructions are configured for compression of data from the relationaldatabase.
 30. The non-transitory computer readable storage medium ofclaim 27, wherein the program of database machine code instructions areconfigured for decompression of data from the relational database. 31.The non-transitory computer readable storage medium of claim 27, whereinthe program of database machine code instructions are configured forencryption of data from the relational database.
 32. The non-transitorycomputer readable storage medium of claim 27, wherein the program ofdatabase machine code instructions are configured for a projectionoperation.
 33. The non-transitory computer readable storage medium ofclaim 27, wherein the program of database machine code instructions areconfigured for a predicate filtering operation.
 34. The non-transitorycomputer readable storage medium of claim 27, wherein the program ofdatabase machine code instructions are configured for a groupbyaggregation.
 35. The non-transitory computer readable storage medium ofclaim 27, wherein the program of database machine code instructions areconfigured for an orderby limit operation.
 36. The non-transitorycomputer readable storage medium of claim 27, wherein the program ofdatabase machine code instructions are configured for a hash joinoperation.
 37. The non-transitory computer readable storage medium ofclaim 27, wherein the program of database machine code instructions areconfigured for a sorted merge join operation.
 38. The non-transitorycomputer readable storage medium of claim 27, wherein the program ofdatabase machine code instructions are configured for a merge sortoperation.